How to: Use the Advanced Filter.
Solution:
Select the 'Data' menu and select 'Filter' to display a submenu. Select 'Advanced Filter' from the submenu. Specify the desired criteria in the 'Advanced Filter' dialog box.
1) Select any cell in a list.
2) Select the 'Data' menu and select 'Filter'. (A submenu appears.)
the Filter submenu
3) Select 'Advanced Filter' from the submenu. (The Advanced Filter dialog box appears.)
4) Select the desired option in the 'Action' group:
Selecting the action option
a) Select the 'Filter the List, in-place' radio button to show only the matching rows in the original list area and hide the rows that do not meet the criteria.
b) Select the 'Copy to Another Location' radio button to copy the filtered data to another area of the worksheet so that the original list remains unchanged.
5) Type the range of the list to filter in the 'List Range' box.
NOTE: By default, Excel automatically displays the range of the list selected in Step 1). However, the range can be changed as necessary.
6) Type the range containing the criteria in the 'Criteria Range' box.
7) If the 'Copy to Another Location' radio button was selected in Step 4), type the range to which to copy the filtered data in the 'Copy to' box.
8) (Optional) Select the 'Unique Records Only' check box to filter out duplicate records in the specified range.
the Unique Records Only check box
NOTE: If the 'Unique Records Only' check box is unchecked, all records that meet the criteria will display, including duplicates.
9) Click 'OK'.
